﻿using System;
using System.Text;

using NORM.Common;
using NORM.DataBase;
using NORM.Entity;

using System.Collections.Generic;
using System.Reflection;
using System.Diagnostics;

namespace NORM.SQLObject
{
    /// <summary>
    /// 对象查询脚本
    /// </summary>
    public partial class OQL : OQLComponent, IDisposable
    {
        internal int _limit = 0;//限制数
        internal bool _join = false;//是否有联表查询的标识 
        internal string _commandcode = "SELECT";
        protected List<string> modelnameArray = new List<string>(); //有联表操作时记录表名         
        protected internal Stack<TableField> fieldStack = new Stack<TableField>(); //字段堆栈

        private EntityBase modelobject;
        internal EntityBase Modelobject
        {
            get { return modelobject; }
        }

        private object[] _selectfields;
        internal object[] SelectFields
        {
            get { return _selectfields; }
            set { _selectfields = value; }
        }

        private TableField[] _updatefields;
        internal TableField[] UpdateFields
        {
            get { return _updatefields; }
            set { _updatefields = value; }
        }
        
        private Condition[] _conditions;      
        internal Condition[] Conditions
        {
            set { _conditions = value; }
            get { return _conditions; }
        }

        private object[] _orderbies;
        internal object[] Orderbies
        {
            get { return _orderbies; }
            set { _orderbies = value; }
        }

        private OsqlParameter[] _parameters;
        internal OsqlParameter[] Parameters
        {
            set { _parameters = value; }
            get { return _parameters; }
        }

        protected OQL _childoql = null;
        /// <summary>
        /// 子查询OQL
        /// </summary>
        public OQL ChildOql
        {
            get { return _childoql; }
        }

        protected OQL _parentoql = null;
        protected OQL ParentOql
        {
            get { return _parentoql; }
            set { _parentoql = value; }
        }

        private bool _isend = false;
        internal bool IsEnd
        {
            get { return _isend; }
            set { _isend = value; }
        }

        private int _recordcount = 0;
        public int RecordCount
        {
            internal set { _recordcount = value; }
            get { return _recordcount; }
        }

        private int _totalrecordcount = 0;
        public int TotalRecordCount
        {
            internal set { _totalrecordcount = value; }
            get { return _totalrecordcount; }
        }

        internal string sql = string.Empty;
        public override string ToString()
        {
            return this.sql;
        }

        private void OnPropertyChanged(EntityBase sender, EntityPropertyArgs e)
        {
            if (e.PropertyArgsType == 1)
            {
                fieldStack.Push(new TableField()
                {
                    Field = e.Field,
                    FieldType = e.Type,
                    Entity = sender
                });
            }              
        }

        public OQL(EntityBase model)
        {
            this.Key = "OQL";
            this.Guid = "8160ee7c-67e4-4932-8097-01ee845c2f3d";
            modelnameArray = new List<string>();
            modelnameArray.Add(model.TableName);
            modelobject = model;
            if (modelobject.PropertyOnGetting == null)
                modelobject.PropertyOnGetting += new PropertyChangedHandler(OnPropertyChanged);
        }

        public static OQL From(EntityBase model)
        {
            return new OQL(model);
        }

        public OQL Insert()
        {
            List<OsqlParameter> paramters = new List<OsqlParameter>();

            StringPlus cmdText = new StringPlus();

            cmdText.Append("INSERT INTO ");
            cmdText.Append("[" + modelobject.TableName + "] ");

            Type type = modelobject.GetType(); //typeof(T);
            //System.Reflection.PropertyInfo[] PropertiesInfo = type.GetProperties();
            System.Reflection.PropertyInfo[] PropertiesInfo = CacheProperties.cacheInstance.GetProperties(type);

            cmdText.Append("( ");
            string feilds = string.Empty;
            foreach (System.Reflection.PropertyInfo pi in PropertiesInfo)
            {
                object o = pi.GetValue(modelobject, null);
                if (pi.Name != "TableName" && pi.Name != "Properties")
                {

                    if (o != null && !DBNull.Value.Equals(o)
                        && !o.ToString().Equals("0001-01-01 0:00:00")
                        && !DataValue.GuidEmpty.Equals(o))
                    {
                        object[] attrs = pi.GetCustomAttributes(false);
                        if (attrs != null && attrs.Length > 0 &&
                            attrs[0].ToString().Equals("Identify"))
                        {
                            //自增列
                        }
                        else
                        {
                            feilds += "[" + pi.Name + "]" + " ,";
                        }
                    }
                    
                }
            }
            feilds = feilds.TrimEnd(',');
            cmdText.Append(feilds);
            cmdText.Append(") ");

            cmdText.Append("VALUES( ");
            string values = string.Empty;
            foreach (System.Reflection.PropertyInfo pi in PropertiesInfo)
            {
                object o = pi.GetValue(modelobject, null);
                if (pi.Name != "TableName" && pi.Name != "Properties")
                {
                    if (o != null && !DBNull.Value.Equals(o)
                        && !o.ToString().Equals("0001-01-01 0:00:00")
                        && !DataValue.GuidEmpty.Equals(o))
                    {
                        object[] attrs = pi.GetCustomAttributes(false);
                        if (attrs != null && attrs.Length > 0)
                        {
                            List<string> attrs_list = ArrayPlus.ToSList(attrs);
                            if (attrs != null && attrs.Length > 0 &&
                                attrs[0].ToString().Equals("Identify"))//|| modelobject.PrimaryKey.Contains(pi.Name)
                            {
                                //自增列
                            }
                            else
                            {
                                if (attrs_list.Contains("ShortDateTime"))
                                {
                                    values += "@" + pi.Name + " ,";
                                    paramters.Add(new OsqlParameter(pi.Name, Convert.ToDateTime(o).ToString("yyyy-MM-dd")));
                                }
                                //新定义短时间类型 ShortDateTime
                                else if (attrs_list.Contains("Type::Sdt"))
                                {
                                    values += "@" + pi.Name + " ,";
                                    paramters.Add(new OsqlParameter(pi.Name, OsqlParameterDataType.ShortDateTime, Convert.ToDateTime(o).ToString("yyyy-MM-dd")));
                                }
                                else if (attrs_list.Contains("Type::Bit"))
                                {
                                    values += "@" + pi.Name + " ,";
                                    paramters.Add(new OsqlParameter(pi.Name, OsqlParameterDataType.Bit, new BitString((bool)o).ToString()));
                                }
                                else if (attrs_list.Contains("Type::Box"))
                                {
                                    values += "@" + pi.Name + " ,";
                                    paramters.Add(new OsqlParameter(pi.Name, o));
                                }
                                else
                                {
                                    values += "@" + pi.Name + " ,";
                                    paramters.Add(new OsqlParameter(pi.Name, o));
                                }
                            }
                        }
                        else
                        {
                            values += "@" + pi.Name + " ,";
                            paramters.Add(new OsqlParameter(pi.Name, o));
                        }
                    }
                }
            }

            values = values.TrimEnd(',');
            cmdText.Append(values);
            cmdText.Append(")");

            this.sql = cmdText.Value;
            if (paramters.Count > 0)
                this._parameters = paramters.ToArray();

            this._commandcode = "INSERT";

            return this;
        }

        public OQL Update(TableField[] Fiels = null)
        {
            StringPlus cmdText = new StringPlus();

            List<OsqlParameter> paramters = new List<OsqlParameter>();
            List<Condition> conditions = new List<Condition>();

            Type type = modelobject.GetType(); //typeof(T);
            //System.Reflection.PropertyInfo[] Properties = type.GetProperties();
            System.Reflection.PropertyInfo[] PropertiesInfo = CacheProperties.cacheInstance.GetProperties(type);

            string _sql = "UPDATE [" + modelobject.TableName + "] SET ";

            if (Fiels != null)
            {
                #region 修改指定的字段

                this._updatefields = Fiels;

                for (int i = 0; i < Fiels.Length; i++)
                {
                    TableField tf = Fiels[i] as TableField;
                    if (modelobject.ContainsKeys(tf.Field))
                    {
                        PropertyInfo pi = CacheProperties.cacheInstance.GetProperty(type, tf.Field);
                        if (pi == null)
                            throw new NORMException(ExceptionType.OQLExceptin, "字段:" + tf.Field + "找不到");

                        if (pi.Name != "TableName" && pi.Name != "Properties")
                        {
                            object[] attrs = pi.GetCustomAttributes(false);

                            List<string> customattrs = new List<string>();

                            if (attrs != null)
                            {
                                foreach (object attr in attrs)
                                {
                                    customattrs.Add(attr + "");
                                }
                            }

                            if (customattrs.Contains("Identify") ||
                            modelobject.PrimaryKey.Contains(pi.Name))
                            {
                                //不用修改                         
                            }
                            else
                            {
                                object value_d = tf.FieldValue;

                                if (value_d != null)
                                {
                                    Condition condition = new Condition();
                                    condition.Field = pi.Name;
                                    condition.Value = value_d;
                                    condition.Comparison = "=";

                                    _sql += "[" + pi.Name + "]=@" + pi.Name + " ,";

                                    //兼容老版本ShortDateTime
                                    if (customattrs.Contains("ShortDateTime"))
                                    {
                                        condition.Value = Convert.ToDateTime(value_d).ToString("yyyy-MM-dd");
                                        paramters.Add(new OsqlParameter(pi.Name, Convert.ToDateTime(value_d).ToString("yyyy-MM-dd")));
                                    }
                                    //新定义短时间类型 ShortDateTime
                                    else if (customattrs.Contains("Type::Sdt"))
                                    {
                                        condition.Value = Convert.ToDateTime(value_d).ToString("yyyy-MM-dd");
                                        paramters.Add(new OsqlParameter(pi.Name, OsqlParameterDataType.ShortDateTime, Convert.ToDateTime(value_d).ToString("yyyy-MM-dd")));
                                    }
                                    //布尔类型 Bit
                                    else if (customattrs.Contains("Type::Bit"))
                                    {
                                        condition.Value = new BitString((bool)value_d).ToString();
                                        paramters.Add(new OsqlParameter(pi.Name, OsqlParameterDataType.Bit, new BitString((bool)value_d).ToString()));
                                    }
                                    else
                                    {
                                        paramters.Add(new OsqlParameter(pi.Name, value_d));
                                    }

                                    conditions.Add(condition);

                                }

                            }


                        }
                    }
                }

                _sql = _sql.TrimEnd(',');

                cmdText.Append(_sql);

                #endregion
            }
            else
            {
                #region Fiels 为空时

                List<TableField> _tfs = new List<TableField>();

                foreach (System.Reflection.PropertyInfo pi in PropertiesInfo)
                {
                    if (pi.Name != "TableName" && pi.Name != "Properties")
                    {
                        object[] attrs = pi.GetCustomAttributes(false);

                        List<string> customattrs = new List<string>();

                        if (attrs != null)
                        {
                            foreach (object attr in attrs)
                            {
                                customattrs.Add(attr + "");
                            }
                        }

                        if (customattrs.Contains("Identify") ||
                            modelobject.PrimaryKey.Contains(pi.Name))
                        {
                            //不用修改                         
                        }
                        else
                        {
                            Type _type = pi.PropertyType;
                            object value_d = pi.GetValue(modelobject, null);
                            if (value_d != null)
                            {
                                //Condition condition = new Condition();
                                //condition.Field = pi.Name;
                                //condition.Value = value_d;
                                //condition.Comparison = "=";

                                TableField tf = new TableField();
                                tf.Field = pi.Name;
                                tf.Entity = modelobject;
                                tf.FieldValue = value_d;

                                _sql += "[" + pi.Name + "]=@" + pi.Name + " ,";

                                //兼容老版本ShortDateTime
                                if (customattrs.Contains("ShortDateTime"))
                                {
                                    //condition.Value = Convert.ToDateTime(value_d).ToString("yyyy-MM-dd");
                                    paramters.Add(new OsqlParameter(pi.Name, Convert.ToDateTime(value_d).ToString("yyyy-MM-dd")));
                                }
                                //新定义短时间类型 ShortDateTime
                                else if (customattrs.Contains("Type::Sdt"))
                                {
                                    //condition.Value = Convert.ToDateTime(value_d).ToString("yyyy-MM-dd");
                                    paramters.Add(new OsqlParameter(pi.Name, OsqlParameterDataType.ShortDateTime, Convert.ToDateTime(value_d).ToString("yyyy-MM-dd")));
                                }
                                //布尔类型 Bit
                                else if (customattrs.Contains("Type::Bit"))
                                {
                                    //condition.Value = new NpgsqlTypes.BitString((bool)value_d).ToString();
                                    paramters.Add(new OsqlParameter(pi.Name, OsqlParameterDataType.Bit, new BitString((bool)value_d).ToString()));
                                }
                                else
                                {
                                    //condition.Value = value_d;
                                    paramters.Add(new OsqlParameter(pi.Name, value_d));
                                }

                                //conditions.Add(condition);
                                _tfs.Add(tf);

                            }
                        }

                    }
                }

                _sql = _sql.TrimEnd(',') + " WHERE ";

                foreach (string Key in modelobject.PrimaryKey)
                {
                    _sql += "[" + Key + "]=@" + Key + " AND";
                    paramters.Add(new OsqlParameter(Key, type.GetProperty(Key).GetValue(modelobject, null)));

                    Condition condition = new Condition();
                    condition.Field = Key;
                    condition.Value = type.GetProperty(Key).GetValue(modelobject, null);
                    condition.Comparison = "=";
                    conditions.Add(condition);
                }

                if (_sql.Contains("AND"))
                {
                    _sql = _sql.Substring(0, _sql.LastIndexOf("AND"));
                }

                cmdText.Append(_sql);

                this._updatefields = _tfs.ToArray();

                #endregion
            }            

            this.sql = cmdText.Value;
            if (paramters.Count > 0)
                this._parameters = paramters.ToArray();

            this._conditions = conditions.ToArray();

            this._commandcode = "UPDATE";

            return this;

        }

        public OQL Update(Func<OQLUpdate, bool> func)
        {
            if (!func(new OQLUpdate(this)))
                throw new NORMException(ExceptionType.OQLExceptin, "构造OQL失败");
            return this;
        }

        public OQL Delete()
        {            
            this.sql = "DELETE FROM [" + modelobject.TableName + "] ";
            this._commandcode = "DELETE";
            return this;
        }
        
        public OQL Select(object[] Field = null)
        {
            string _sql = "SELECT ";

            Type _type = modelobject.GetType();

            if (Field != null)
            {
                this._selectfields = Field;
                string field = string.Empty;             
                foreach (object fi in Field)
                {
                    if (!_join)
                    {
                        if (fi.GetType().Equals(typeof(TableField)))
                        {
                            field = ((TableField)fi).Field;
                        }
                        else
                        {
                            field = fi.ToString();
                        }

                        if (field.ToUpper().Contains("AS"))
                        {
                            field = field.Replace("as", "AS").Replace("As", "AS").Replace("as", "AS");
                            string asname = field.Substring(field.LastIndexOf("AS") + 2).Trim();
                            string orname = field.Substring(0, field.LastIndexOf("AS")).Trim();
                            _sql += "[" + orname + "]" + " AS [" + asname + "] ,";
                        }
                        else
                        {
                            _sql += "[" + field + "]" + " ,";
                        }
                    }
                    else
                    {
                        field = fi.ToString();

                        string t = string.Empty;
                        GetAsTableName(field.Substring(0, field.LastIndexOf(".")), out t);
                        string f = field.Substring(field.LastIndexOf(".") + 1);

                        if (f.ToUpper().Contains("AS"))
                        {
                            f = f.Replace("as", "AS").Replace("As", "AS").Replace("as", "AS");
                            string asname = field.Substring(field.LastIndexOf("AS") + 2).Trim();
                            string orname = f.Substring(0, f.LastIndexOf("AS")).Trim();
                            _sql += ""+t+".[" + orname + "]" + " AS [" + asname + "] ,";
                        }
                        else
                        {
                            _sql += "" + t + ".[" + f + "]" + " ,";
                        }

                    }

                    
                }

                _sql = _sql.TrimEnd(',');
            }
            else
            {
                if (!_join)
                {
                    //PropertyInfo[] PropertiesInfo = _type.GetProperties();
                    PropertyInfo[] PropertiesInfo = CacheProperties.cacheInstance.GetProperties(_type);
                    foreach (PropertyInfo pi in PropertiesInfo)
                    {
                        if (pi.Name != "TableName" && pi.Name != "Properties")
                            _sql += "[" + pi.Name + "]" + " ,";
                    }
                }
                else
                {
                    _sql += " * ";
                }

                _sql = _sql.TrimEnd(',');
            }

            if (!_join)
                _sql += " FROM [" + modelobject.TableName + "] ";

            this.sql = _sql + sql;

            this._commandcode = "SELECT";

            return this;
        }

        public OQL Select(object[] Field, bool Distinct)
        {
            string _sql = "SELECT DISTINCT ";
            
            Type _type = modelobject.GetType();                     

            if (Field != null)
            {
                this._selectfields = Field;
                string field = string.Empty;
                foreach (object fi in Field)
                {
                    field = fi.ToString();
                    if (!_join)
                    {
                        if (field.ToUpper().Contains("AS"))
                        {
                            field = field.Replace("as", "AS").Replace("As", "AS").Replace("as", "AS");
                            string asname = field.Substring(field.LastIndexOf("AS") + 2).Trim();
                            string orname = field.Substring(0, field.LastIndexOf("AS")).Trim();
                            _sql += "[" + orname + "]" + " AS [" + asname + "] ,";
                        }
                        else
                        {
                            _sql += "[" + field + "]" + " ,";
                        }
                    }
                    else
                    {
                        string t = string.Empty;
                        GetAsTableName(field.Substring(0, field.LastIndexOf(".")), out t);
                        string f = field.Substring(field.LastIndexOf(".") + 1);

                        if (f.ToUpper().Contains("AS"))
                        {
                            f = f.Replace("as", "AS").Replace("As", "AS").Replace("as", "AS");
                            string asname = field.Substring(field.LastIndexOf("AS") + 2).Trim();
                            string orname = field.Substring(0, field.LastIndexOf("AS")).Trim();
                            _sql += "" + t + ".[" + orname + "]" + " AS [" + asname + "] ,";
                        }
                        else
                        {
                            _sql += "" + t + ".[" + f + "]" + " ,";
                        }
                    }
                }

                _sql = _sql.TrimEnd(',');
            }
            else
            {
                if (!_join)
                {
                    //PropertyInfo[] PropertiesInfo = _type.GetProperties();
                    PropertyInfo[] PropertiesInfo = CacheProperties.cacheInstance.GetProperties(_type);
                    foreach (PropertyInfo pi in PropertiesInfo)
                    {
                        if (pi.Name != "TableName" && pi.Name != "Properties")
                            _sql += "[" + pi.Name + "]" + " ,";
                    }
                }
                else
                {
                    _sql += " * ";
                }

                _sql = _sql.TrimEnd(',');
            }

            if (!_join)
                _sql += " FROM [" + modelobject.TableName + "] ";

            this.sql = _sql + sql;

            this._commandcode = "SELECT";

            return this;
        }

        public OQL Select(Func<object> func)
        {
            var entity = func();

            if (entity != null)
            {
                Type type = entity.GetType();

                if (type.Name == "String")
                {
                    string str = entity.ToString();
                    if (str.Contains(","))
                    {
                        return Select(str.Split(','));
                    }
                    else
                    {
                        return Select(new string[] { str });
                    }
                }
                else if (type.Name == "String[]")
                {
                    return Select((String[])entity);
                }
                else if (type.Name == "List`1")
                {
                    if (type.Equals(typeof(List<TableField>)))
                    {                       
                        return Select(((List<TableField>)entity).ToArray());
                    }
                    else
                    {
                        return Select(((List<object>)entity).ToArray());
                    }
                }
                else if (type.Name.StartsWith("<>f__AnonymousType"))
                {
                    PropertyInfo[] properties = CacheProperties.cacheInstance.GetProperties(type);
                    List<string> list = new List<string>();
                    foreach (PropertyInfo pi in properties)
                        list.Add(pi.Name);

                    return Select(list.ToArray());
                }
                else
                {
                    //
                }

            }

            this._commandcode = "SELECT";

            return this;
        }

        public OQL Where(Condition[] Conditions)
        {
            if (this.sql.Contains("WHERE"))
            {
                this.sql = this.sql.Substring(0, this.sql.LastIndexOf("WHERE"));
            }

            this.sql += "WHERE";

            this._conditions = Conditions;

            List<OsqlParameter> _parameters = new List<OsqlParameter>();

            WhereToParse(this, 0, _parameters);
            
            if (_parameters.Count > 0)
                this._parameters = _parameters.ToArray();

            if (this._commandcode == "UPDATE")
            {
                this.sql = this.sql.Replace("WHERE AND", "WHERE ");
                List<Condition> _conditionarray = new List<Condition>();
                foreach (Condition c in this._conditions)
                {
                    if (!ArrayPlus.ContainsTableFieldKey(this._updatefields, c.Field))
                    {
                        _conditionarray.Add(c);
                    }
                }
                if (_conditionarray != null && _conditionarray.Count > 0)
                    _conditionarray[0].Relation = "";

                this._conditions = _conditionarray.ToArray();

            }

            return this;
        }

        public OQL Where(Func<List<Condition>> func)
        {            
            return Where(func().ToArray());
        }

        public OQL Where(Func<OQLCondition, Boolean> func)
        {
            if (!func(new OQLCondition(this)))
            {
                throw new Exception("Func<OQLCondition, Boolean> 返回为False");
            }
            return this;
        }

        public OQL Where(List<Condition> Conditions)
        {
            return Where(Conditions.ToArray());
        }

        public OQL OrderBy(object[] OrderBy)
        {
            if (this.sql.Contains("ORDER BY"))
            {
                this.sql = this.sql.Substring(0, this.sql.LastIndexOf("ORDER BY"));
            }
            this.sql += " ORDER BY ";

            this._orderbies = OrderBy;

            OrderByToParse(this);

            return this;
        }

        public OQL OrderBy(Func<OQLOrder, Boolean> func)
        {
            if (!func(new OQLOrder(this)))
            {
                throw new Exception("Func<OQLOrder, Boolean> 返回为False");
            }
            return this;
        }

        public OQL GroupBy(object[] GroupBy)
        {
            this.sql += " GROUP BY ";

            foreach (object g in GroupBy)
            {
                string gr = g + "";
                this.sql += "[" + gr + "]" + " ,";
            }

            this.sql = sql.TrimEnd(',');

            return this;
        }

        public OQL Limit(int Size)
        {
            this._limit = Size;
            this.sql += " Limit " + Size + "";
            return this;
        }

        public OQL End
        {
            get
            {                  
                this.sql = this.sql.TrimEnd(';') + ";";
                this._isend = true;
                Dispose();
      
                return this;
            }
        }

        public void Dispose()
        {
            if (modelobject.PropertyOnGetting != null)
                modelobject.PropertyOnGetting -= new PropertyChangedHandler(OnPropertyChanged);
            fieldStack.Clear();
        }

    }

}
